resources

  • NEDL
  • MSTR evaluation
In [1]:
import datetime
from IPython.display import Markdown as md
import ccxt
import yfinance as yf
import numpy as np
import pandas as pd
import requests
import re
import plotly
import plotly.express as px
import statsmodels.api as sm
import scipy.optimize as spop
In [2]:
now = datetime.datetime.now()
md("Last Updated at {}".format(now.strftime("%Y/%m/%d %H:%M")))
Out[2]:

Last Updated at 2022/08/19 01:23

In [3]:
exchange = ccxt.kraken ({"enableRateLimit": True})
candles = exchange.fetch_ohlcv("BTC/USD", "1d")
df_btc = pd.DataFrame(candles)
df_btc = df_btc.iloc[:, [0, 4]]
df_btc.columns = ["time", "btc_usd_close"]
df_btc["time"] = pd.to_datetime(df_btc["time"], unit="ms")
df_btc["str_time"] = pd.to_datetime(df_btc["time"], unit="ms").dt.strftime("%Y/%m/%d")

Price chart of BTC/USD¶

In [4]:
fig = px.line(df_btc, x="time", y="btc_usd_close")
fig.show()

Price chart of MSTR¶

In [5]:
# match start and end date with btc/usd
start_date = list(map(int, df_btc["str_time"].iloc[0].split('/')))
end_date = list(map(int, df_btc["str_time"].iloc[-1].split('/')))
start = datetime.datetime(start_date[0], start_date[1], start_date[2])
end = datetime.datetime(end_date[0], end_date[1], end_date[2])
print(start, end)

# download the stock price 
stock = "MSTR"
df_mstr = yf.download(stock, start=start, end=end, progress=False).reset_index()

2020-08-30 00:00:00 2022-08-19 00:00:00
In [6]:
df_mstr = df_mstr[["Date",  "Close"]].rename(columns = {"Date": "time", "Close": "mstr_close"})
df_mstr["str_time"] = df_mstr["time"].dt.strftime("%Y/%m/%d")
In [7]:
fig = px.line(df_mstr, x="time", y="mstr_close")
fig.show()
In [8]:
# 400 / month budget
# 35-40% from ATH: 175/week
# 40-45% from ATH: 260/week
# 45-50% from ATH: 425/week
# >50% from ATH: /week

def drop_from_ath():
    # function to return 
    url = "https://www.coingecko.com/en/coins/bitcoin"
    headers = {'User-agent': 'Mozilla/5.0'}
    try:
        response = requests.get(url=url, headers=headers)
        response.encoding = response.apparent_encoding

    except requests.exceptions.RequestException as e:
        raise SystemExit(e)
    tmp = pd.read_html(response.text)[0]
    ath_percentage_str = tmp[tmp[0].str.contains("All-Time High")][1].values[0].split()[1]
    ath_percentage_num = float(re.findall(r"[-+]?\d*\.\d+|\d+", ath_percentage_str)[0])
    return ath_percentage_num

Cointegration test¶

In [9]:
df = pd.merge(df_btc, df_mstr, on="str_time", how="inner")
In [10]:
df_price = df[["time_x", "btc_usd_close", "mstr_close"]]
df_price = df_price.rename(columns={"time_x": "date", "btc_usd_close": "BTC", "mstr_close":"MSTR"})
In [11]:
df_returns = pd.DataFrame()
for item in ["BTC", "MSTR"]:
    df_returns[item] = np.append(df_price[item][1:].reset_index(drop=True)/df_price[item][:-1].reset_index(drop=True) -1, 0)
In [12]:
df_price = df_price.loc[(df_price["date"] > "2021-01-01"), :]
df_price["MSTR_BTC_ratio"] = np.log(df_price["MSTR"]) - np.log(df_price["BTC"])
fig = px.line(df_price, x="date", y="MSTR_BTC_ratio")
fig.show()
In [13]:
df_price["ratio_zscore"] = (df_price["MSTR_BTC_ratio"] - np.mean(df_price["MSTR_BTC_ratio"])) / np.std(df_price["MSTR_BTC_ratio"])
fig = px.line(df_price, x="date", y="ratio_zscore")
fig.add_hline(y=np.mean(df_price["ratio_zscore"]))
fig.show()
In [14]:
# initializing arrays
gross_returns = np.array([])
net_returns = np.array([])
t_stats = np.array([])
fair_values = np.array([])
diff_values = np.array([])
a_opts = np.array([])
b_opts = np.array([])
# rolling window
window = 256
t_threshold = 0.05
fee = 0.001 # 0.1%

for t in range(window, len(df_price)):
    # define unit root function
    # MSTR = a + b * BTC
    def unit_root(b):
        a = np.average(df_price["MSTR"][t-window:t] - b * df_price["BTC"][t-window:t])
        fair_value = a + b * df_price["BTC"][t-window:t]
        diff = np.array(fair_value - df_price["MSTR"][t-window:t])
        # difference in difference
        diff_diff = diff[1:] - diff[:-1]
        # regress difference in difference by lagged difference
        lin_reg = sm.OLS(diff_diff, diff[:-1])
        result = lin_reg.fit()
        # return Dicky-Fuller t-stat
        return result.params[0]/result.bse[0]
    
    # optimize cointegration parameters
    result1 = spop.minimize(unit_root, df_price["MSTR"][t] / df_price["BTC"][t], method="Nelder-Mead")
    t_opt = result1.fun
    b_opt = float(result1.x)
    a_opt = np.average(df_price["MSTR"][t-window:t] - b_opt * df_price["BTC"][t-window:t])
    fair_value = a_opt + b_opt * df_price["BTC"][t]
    diff_value = fair_value - df_price["MSTR"][t]
    
    # simulate trading
    if t == window:
        old_signal = 0
    if t_opt > t_threshold:
        signal = 0
        gross_return = 0
    else:
        signal = np.sign(fair_value - df_price["BTC"][t])
        gross_return = signal * df_returns["MSTR"][t] - signal * df_returns["BTC"][t]
    fees = fee * abs(signal - old_signal)
    net_return = gross_return - fees
    gross_returns = np.append(gross_returns, gross_return)
    net_returns = np.append(net_returns, net_return)
    t_stats = np.append(t_stats, t_opt)
    fair_values = np.append(fair_values, fair_value)
    diff_values = np.append(diff_values, diff_value)
    a_opts = np.append(a_opts, a_opt)
    b_opts = np.append(b_opts, b_opt)
    
    # simple logging
    #print('day ' + str(df_price.index[t]))
    #print('----------')
    #if signal == 0:
    #    print('No trading')
    #elif signal == 1:
    #    print('long BTC, short MSTR.')
    #else:
    #    print('short BTC, long MSTR.')
    #print(f'gross daily return: {round(gross_return*100, 2)}%')
    #print(f'net daily return: {round(net_return*100, 2)}%')
    #print(f'cumulative net return: {round(np.prod(1+net_returns)*100 -100, 2)}%')
    #print('----------')
    old_signal = signal
In [15]:
df_result = pd.DataFrame({"gross_returns":np.append(1, np.cumprod(1+gross_returns)), 
                          "net_returns": np.append(1, np.cumprod(1+net_returns))})
df_result["date"] = df_price["date"][window:].reset_index(drop=True)
fig = px.line(df_result, "date", "net_returns")
fig.show()
In [16]:
df_mstr = df_price.iloc[window:, ].copy()
df_mstr.loc[:, "fair_value"] = fair_values
df_mstr.loc[:, "diff_value"] = diff_values
df_mstr.loc[:, "t_stat"] = t_stats
df_mstr.loc[:, "a_opt"] = a_opts
df_mstr.loc[:, "b_opt"] = b_opts
#df_mstr.loc[abs(df_mstr["fair_value"])>10**8, "fair_value"] = 0
In [17]:
fig = px.line(df_mstr, "date", df_mstr["b_opt"])
fig.show()

MSTR stock evaluation¶

In [18]:
# Last update 2022/06/11
# https://www.coingecko.com/en/public-companies-bitcoin
BTC_owned = 129218
last_btc_price = df_mstr.iloc[-1]["BTC"]
# https://stockanalysis.com/stocks/mstr/statistics/
num_shares = 11.30 * 10**6
last_mstr_price = df_mstr.iloc[-1]["MSTR"]
# MSTR intrinsic value
mstr_val = 1200000000
print(f"BTC price: {last_btc_price}")
print(f"MSTR close price: {last_mstr_price}")
print(f"BTC per share: {BTC_owned / num_shares}")
print(f"Spot BTC price: {last_btc_price * BTC_owned / num_shares}")
print(f"Discount/Premium: {round(((last_mstr_price * num_shares) / (last_btc_price * BTC_owned) -1) * 100, 2)}%")

BTC price: 23192.3
MSTR close price: 324.3800048828125
BTC per share: 0.011435221238938052
Spot BTC price: 265.209081539823
Discount/Premium: 22.31%